package utils;

import domain.IndustryStatus;
import domain.StockVo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by ibm on 2018/5/24.
 */
public class JdbcUtil {
    //声明Connection对象
    public static Connection con;
    private static PreparedStatement psql = null;// 预编译对象
    private  static Statement stmt = null;
    private static ResultSet rs = null;// 结果集对象
    private static CallableStatement csmt = null;// 过程对象

    public static void insertConcept(Long concept_id,String name, String desc,String question_answers){
        try {

            //1.getConnection()方法，连接MySQL数据库！！
            con = getConnetion();
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象，用来执行SQL语句！！
            //Statement statement = con.createStatement();
            //预处理添加数据，其中有两个参数--“？”
            psql = con.prepareStatement("insert into concept_desc (concept_name,concept_desc,question_answers,concept_id) "
                    + "values(?,?,?,?)");
            psql.setString(1, name);
            psql.setString(2, desc);
            psql.setString(3, question_answers);
            psql.setLong(4, concept_id);
            System.out.println(psql);
            psql.executeUpdate();
            con.close();
        } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            System.out.println("数据库数据成功获取！！");
        }

    }

    public static void insertStock(Long id, List<StockVo> list){
        try {

            //1.getConnection()方法，连接MySQL数据库！！
            con = getConnetion();
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象，用来执行SQL语句！！
            //Statement statement = con.createStatement();
            //预处理添加数据，其中有两个参数--“？”
            for (StockVo stockVo : list){
                psql = con.prepareStatement("insert into concept_stock (stock_code,concept_id,img_url) "
                        + "values(?,?,?)");
                psql.setString(1, stockVo.getStockCode());
                psql.setLong(2, id);
                psql.setString(3, stockVo.getDesc_url_real());
                psql.executeUpdate();
            }

            con.close();
        } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            System.out.println("数据库数据成功获取！！");
        }

    }

    public static void insert(Long id){
        try {

            //1.getConnection()方法，连接MySQL数据库！！
            con = getConnetion();
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象，用来执行SQL语句！！
            //Statement statement = con.createStatement();
            //预处理添加数据，其中有两个参数--“？”
            psql = con.prepareStatement("insert into concept_data (concept_id) "
                    + "values(?)");
            psql.setLong(1, id);
            psql.executeUpdate();
            con.close();
        } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            System.out.println("数据库数据成功获取！！");
        }

    }


    public static List<Long> executeQuery(){
        List<Long> conceptIds = new ArrayList<Long>();
        try {
            stmt = getConnetion().createStatement();

            // Extract records without any condition.
            System.out.println("Fetching records without condition...");
            //String sql = "SELECT concept_id FROM concept_data";
            String sql = "SELECT concept_id FROM concept_data";
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                //Retrieve by column name
                Long id = rs.getLong("concept_id");
                conceptIds.add(id);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conceptIds;
    }

    /**
     *  获得连接对象
     * @return
     */
    public static Connection getConnetion() {
        try {

            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager
                    .getConnection("jdbc:mysql://localhost:3306/concept_info","root","123");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            System.out.println("找不到驱动");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            System.out.println("数据库连接失败");
        }
        return con;
    }

    //单一插入行业
    public static void insertIndustryStatus(int industry_type, String industry_parent, String industry_name){
        try {

            //1.getConnection()方法，连接MySQL数据库！！
            con = getConnetion();
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象，用来执行SQL语句！！
            //Statement statement = con.createStatement();
            //预处理添加数据，其中有两个参数--“？”
            psql = con.prepareStatement("insert into industry_status (industry_type,industry_parent,industry_name)"
                    + "values(?,?,?)");
            psql.setInt(1, industry_type);
            psql.setString(2, industry_parent);
            psql.setString(3, industry_name);

            System.out.println(psql);
            psql.executeUpdate();
            con.close();
        } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            System.out.println("数据库数据成功获取！！");
        }

    }

    //批量插入行业
    public static void insertBatchIndustryStatus(List<IndustryStatus> industryStatusList){
        try {
            //1.getConnection()方法，连接MySQL数据库！！
            con = getConnetion();
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象，用来执行SQL语句！！
            //Statement statement = con.createStatement();
            //预处理添加数据，其中有两个参数--“？”

            String sql = "insert into industry_status (industry_type,industry_parent,industry_name) "
                    + "values(?,?,?)";
            psql = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            con.setAutoCommit(false);

            for (IndustryStatus industryStatus: industryStatusList) {
                psql.setInt(1, industryStatus.getIndustry_type());
                psql.setInt(2, industryStatus.getIndustry_parent());
                psql.setString(3, industryStatus.getIndustry_name());
                psql.addBatch();
            }
            psql.executeBatch();
            con.commit();
            con.close();
        } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            System.out.println("数据库数据成功获取！！");
        }

    }

    public static int queryIndustryStatus(String value){

        int id  = 0;
        try {
            con = getConnetion();

            // Extract records without any condition.
            System.out.println("Fetching records with condition...");
            //String sql = "SELECT id FROM concept_data";
            String sql = "SELECT id FROM industry_status WHERE industry_name=?";
            psql = con.prepareStatement(sql);
            psql.setString(1, value);//设置条件id
            rs = psql.executeQuery();

            while(rs.next()){
                //Retrieve by column name
                id = rs.getInt("id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return id;
    }
}
